﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Text;

using V5_DBUtility;
using V5_Model;
namespace V5_DAL.SqlServer {
    public class DALDiyPageClass {

        #region  BaseMethod
        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId() {
            return DbHelperSQL.GetMaxID("ClassID", "S_DiyPageClass");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from S_DiyPageClass");
            strSql.Append(" where ClassID=" + ID);
            return DbHelperSQL.Exists(strSql.ToString());
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(ModelDiyPageClass model) {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.ChannelID != null) {
                strSql1.Append("ChannelID,");
                strSql2.Append("" + model.ChannelID + ",");
            }
            if (model.ClassCode != null) {
                strSql1.Append("ClassCode,");
                strSql2.Append("'" + model.ClassCode + "',");
            }
            if (model.ClassDescription != null) {
                strSql1.Append("ClassDescription,");
                strSql2.Append("'" + model.ClassDescription + "',");
            }
            if (model.ClassID != null) {
                strSql1.Append("ClassID,");
                strSql2.Append("" + model.ClassID + ",");
            }
            if (model.ClassKeyWord != null) {
                strSql1.Append("ClassKeyWord,");
                strSql2.Append("'" + model.ClassKeyWord + "',");
            }
            if (model.ClassName != null) {
                strSql1.Append("ClassName,");
                strSql2.Append("'" + model.ClassName + "',");
            }
            if (model.ClassPhoto != null) {
                strSql1.Append("ClassPhoto,");
                strSql2.Append("'" + model.ClassPhoto + "',");
            }
            if (model.Info != null) {
                strSql1.Append("Info,");
                strSql2.Append("'" + model.Info + "',");
            }
            if (model.IsShow != null) {
                strSql1.Append("IsShow,");
                strSql2.Append("" + model.IsShow + ",");
            }
            if (model.ParentID != null) {
                strSql1.Append("ParentID,");
                strSql2.Append("" + model.ParentID + ",");
            }
            strSql.Append("insert into S_DiyPageClass(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            int result = DbHelperSQL.ExecuteSql(strSql.ToString());
            #region 开始更新 ClassCode
            string SQLString = string.Empty;
            string leftCode = string.Empty, selfCode = string.Empty, parentCode = string.Empty;
            if (result > 0) {
                if (model.ParentID != 0) {
                    parentCode = Convert.ToString("" + DbHelperSQL.GetSingle("Select ClassCode as NewName From S_DiyPageClass Where ClassID=" + model.ParentID));
                }
                SQLString = @"
                select Classcode as NewName from S_DiyPageClass
                where left(Classcode," + parentCode.Length.ToString() + @")='" + parentCode + @"' and Len(ClassCode)=" + Convert.ToString(parentCode.Length + 4) + @"
                order by Classcode desc
            ";
                leftCode = Convert.ToString("" + DbHelperSQL.GetSingle(SQLString));
                if (leftCode.Length > 0) {
                    selfCode = Convert.ToString(Convert.ToInt32(leftCode.Substring(leftCode.Length - 4, 4)) + 1).PadLeft(4, '0');
                }
                else {
                    selfCode = "0001";
                }
                selfCode = parentCode + selfCode;
                result = DbHelperSQL.ExecuteSql("Update S_DiyPageClass Set ClassCode='" + selfCode + "' Where ClassID=" + model.ClassID);
            }
            #endregion
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(ModelDiyPageClass model) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update S_DiyPageClass set ");
            if (model.ChannelID != null) {
                strSql.Append("ChannelID=" + model.ChannelID + ",");
            }
            if (model.ClassCode != null) {
                strSql.Append("ClassCode='" + model.ClassCode + "',");
            }
            if (model.ClassDescription != null) {
                strSql.Append("ClassDescription='" + model.ClassDescription + "',");
            }
            if (model.ClassKeyWord != null) {
                strSql.Append("ClassKeyWord='" + model.ClassKeyWord + "',");
            }
            if (model.ClassName != null) {
                strSql.Append("ClassName='" + model.ClassName + "',");
            }
            if (model.ClassPhoto != null) {
                strSql.Append("ClassPhoto='" + model.ClassPhoto + "',");
            }
            if (model.Info != null) {
                strSql.Append("Info='" + model.Info + "',");
            }
            if (model.IsShow != null) {
                strSql.Append("IsShow=" + model.IsShow + ",");
            }
            if (model.ParentID != null) {
                strSql.Append("ParentID=" + model.ParentID + ",");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where ClassID=" + model.ClassID + " ");
            int result = DbHelperSQL.ExecuteSql(strSql.ToString());
            #region 开始更新
            string SQLString = string.Empty;
            string leftCode = string.Empty, selfCode = string.Empty, parentCode = string.Empty, oldCode = string.Empty, sourceCode = string.Empty;
            SQLString = "Select ClassCode From S_DiyPageClass Where ClassID=" + model.ParentID;
            parentCode = Convert.ToString("" + DbHelperSQL.GetSingle(SQLString));
            SQLString = "Select ClassCode From S_DiyPageClass Where ClassID=" + model.ClassID;
            oldCode = Convert.ToString("" + DbHelperSQL.GetSingle(SQLString));
            sourceCode = oldCode;
            oldCode = oldCode.Substring(0, oldCode.Length - 4);
            if (oldCode != parentCode) {
                SQLString = @"
                select Classcode as NewName from S_DiyPageClass
                where left(Classcode," + parentCode.Length.ToString() + @")='" + parentCode + @"' and Len(ClassCode)=" + Convert.ToString(parentCode.Length + 4) + @"
                order by Classcode desc ";
                leftCode = Convert.ToString("" + DbHelperSQL.GetSingle(SQLString));
                if (leftCode.Length > 0) {
                    selfCode = Convert.ToString(Convert.ToInt32(leftCode.Substring(leftCode.Length - 4, 4)) + 1).PadLeft(4, '0');
                }
                else {
                    selfCode = "0001";
                }
                selfCode = parentCode + selfCode;
                result = DbHelperSQL.ExecuteSql("Update S_DiyPageClass Set ClassCode='" + selfCode + "' Where ClassID=" + model.ClassID);
                result = DbHelperSQL.ExecuteSql("update S_DiyPageClass Set ClassCode='" + selfCode + "'+right(ClassCode,len(ClassCode)-" + sourceCode.Length.ToString() + ") where ClassCode like '" + sourceCode + "%'");
            }
            #endregion
            if (result > 0) {
                return true;
            }
            else {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ID) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from S_DiyPageClass ");
            strSql.Append(" where ClassID=" + ID);
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0) {
                return true;
            }
            else {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public ModelDiyPageClass GetModel(int ID) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from S_DiyPageClass ");
            strSql.Append(" where ClassID=" + ID);
            ModelDiyPageClass model = new ModelDiyPageClass();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null && ds.Tables[0].Rows.Count > 0) {
                if (ds.Tables[0].Rows[0]["ChannelID"] != null && ds.Tables[0].Rows[0]["ChannelID"].ToString() != "") {
                    model.ChannelID = int.Parse(ds.Tables[0].Rows[0]["ChannelID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ClassCode"] != null && ds.Tables[0].Rows[0]["ClassCode"].ToString() != "") {
                    model.ClassCode = ds.Tables[0].Rows[0]["ClassCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ClassDescription"] != null && ds.Tables[0].Rows[0]["ClassDescription"].ToString() != "") {
                    model.ClassDescription = ds.Tables[0].Rows[0]["ClassDescription"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ClassID"] != null && ds.Tables[0].Rows[0]["ClassID"].ToString() != "") {
                    model.ClassID = int.Parse(ds.Tables[0].Rows[0]["ClassID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ClassKeyWord"] != null && ds.Tables[0].Rows[0]["ClassKeyWord"].ToString() != "") {
                    model.ClassKeyWord = ds.Tables[0].Rows[0]["ClassKeyWord"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ClassName"] != null && ds.Tables[0].Rows[0]["ClassName"].ToString() != "") {
                    model.ClassName = ds.Tables[0].Rows[0]["ClassName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ClassPhoto"] != null && ds.Tables[0].Rows[0]["ClassPhoto"].ToString() != "") {
                    model.ClassPhoto = ds.Tables[0].Rows[0]["ClassPhoto"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Info"] != null && ds.Tables[0].Rows[0]["Info"].ToString() != "") {
                    model.Info = ds.Tables[0].Rows[0]["Info"].ToString();
                }
                if (ds.Tables[0].Rows[0]["IsShow"] != null && ds.Tables[0].Rows[0]["IsShow"].ToString() != "") {
                    model.IsShow = int.Parse(ds.Tables[0].Rows[0]["IsShow"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ParentID"] != null && ds.Tables[0].Rows[0]["ParentID"].ToString() != "") {
                    model.ParentID = int.Parse(ds.Tables[0].Rows[0]["ParentID"].ToString());
                }
                return model;
            }
            else {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM S_DiyPageClass ");
            if (strWhere.Trim() != "") {
                strSql.Append(" where  1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion  Method

        #region S_DiyPageClass开始
        public bool DeleteList(string IDlist) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from S_DiyPageClass ");
            strSql.Append(" where ClassID in (" + IDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0) {
                return true;
            }
            else {
                return false;
            }
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder) {
            return this.GetList("*", Top, strWhere, filedOrder);
        }

        public DataSet GetList(int PageSize, int PageIndex, string strWhere, string strOrder, ref int RecordCount) {
            return this.GetList("*", PageSize, PageIndex, strWhere, strOrder, ref RecordCount);
        }

        public DataSet GetList(string strFiled, string strWhere) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select " + strFiled + " ");
            strSql.Append(" FROM S_DiyPageClass ");
            if (strWhere.Trim() != "") {
                strSql.Append(" where 1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetList(string strFiled, int Top, string strWhere, string filedOrder) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0) {
                strSql.Append(" Top " + Top.ToString());
            }
            strSql.Append(" " + strFiled + " ");
            strSql.Append(" FROM S_DiyPageClass ");
            if (strWhere.Trim() != "") {
                strSql.Append(" where 1=1 " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetList(string strFiled, int PageSize, int PageIndex, string strWhere, string strOrder, ref int RecordCount) {
            StringBuilder strSqlCount = new StringBuilder();
            strSqlCount.Append("Select Count(*) as ClassID From S_DiyPageClass ");
            if (strWhere.Trim() != "") {
                strSqlCount.Append(" where 1=1 " + strWhere);
            }
            RecordCount = Convert.ToInt32("0" + DbHelperSQL.GetSingle(strSqlCount.ToString()));
            if (PageIndex <= 1) {
                PageIndex = 1;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Top " + PageSize + " " + strFiled + " from S_DiyPageClass where 1=1 ");
            if (strWhere.Trim() != "") {
                strSql.Append(" " + strWhere);
            }
            if (PageIndex > 1) {
                strSql.Append(" And ClassID Not In (Select Top " + ((PageIndex - 1) * PageSize) + " ClassID From S_DiyPageClass Where 1=1 ");
                if (strWhere.Trim() != "") {
                    strSql.Append(" " + strWhere);
                }
                if (!string.IsNullOrEmpty(strOrder)) {
                    strSql.Append(" Order by " + strOrder);
                }
                strSql.Append(")");
            }
            if (!string.IsNullOrEmpty(strOrder)) {
                strSql.Append(" Order by " + strOrder);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion S_DiyPageClass结束


        public void MoveClass(int ClassID, int isUp) {
            string oldCode = string.Empty;
            oldCode = Convert.ToString("" + DbHelperSQL.GetSingle("Select ClassCode From S_DiyPageClass Where ClassID=" + ClassID));
            string subStr = "substring";
            int codeLen = oldCode.Length;
            if (codeLen > 1) {
                string temp = string.Empty;
                string wStr = "";
                string newStr = "";
                for (int i = 0; i < codeLen; i++)
                    newStr += "-";
                if (codeLen > 4)
                    wStr = " and left(ClassCode," + Convert.ToString(codeLen - 4) + ")='" + oldCode.Substring(0, codeLen - 4) + "'";
                if (isUp == 1)
                    wStr = "select top 1 ClassCode from S_DiyPageClass where len(ClassCode)=" + codeLen.ToString() + " and ClassCode<'" + oldCode + "'" + wStr + " order by ClassCode desc ";
                else
                    wStr = "select top 1 ClassCode from S_DiyPageClass where len(ClassCode)=" + codeLen.ToString() + " and ClassCode>'" + oldCode + "'" + wStr + " order by ClassCode ";
                temp = Convert.ToString("" + DbHelperSQL.GetSingle(wStr));
                if (temp.Length > 1) {
                    //Move Under Column
                    wStr = "update S_DiyPageClass set ClassCode='" + newStr + "'+" + subStr + "(ClassCode," + Convert.ToString(codeLen + 1) + ",len(Classcode)) where left(ClassCode," + codeLen.ToString() + ")='" + temp + "'";
                    DbHelperSQL.ExecuteSql(wStr);
                    //Update Target Column
                    wStr = "update S_DiyPageClass set classcode='" + temp + "'+" + subStr + "(classcode," + Convert.ToString(codeLen + 1) + ",len(Classcode)) where left(classcode," + codeLen.ToString() + ")='" + oldCode + "'";
                    DbHelperSQL.ExecuteSql(wStr);
                    //Update Under Column
                    wStr = "update S_DiyPageClass set classcode='" + oldCode + "'+" + subStr + "(classcode," + Convert.ToString(newStr.Length + 1) + ",len(classcode)) where left(Classcode," + newStr.Length.ToString() + ")='" + newStr + "'";
                    DbHelperSQL.ExecuteSql(wStr);
                    //以下是更新栏目信息 可以不用添加
                }
            }
        }
    }
}